package com.linlang.grasharepro.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.linlang.grasharepro.model.CollectionDomain;
import com.linlang.grasharepro.model.ShareCoenten;
import com.linlang.grasharepro.model.User;
import com.linlang.grasharepro.utils.DataBaseOpenHelper;

import org.ocpsoft.prettytime.PrettyTime;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

/**
 * 发布的内容
 * Created by lenovo on 2016/8/2.
 */
public class ShareCoentenDB {
    public static final String SQLITE_TABLE = "share_coent";

    public static final String PK = "_id";
    public static final String USER_NAME = "userName";
    public static final String TIME = "time";
    public static final String TYPE = "type";
    public static final String MAJOR = "major";
    public static final String TITLE = "title";
    public static final String CONTENT = "content";
    public static final String COLLECTION_COUNT = "collectioncount";
    public static final String MESSAGE_COUNT = "messagecount";
    public static final String SHARE_COUNT = "sharecount";
    public static final String HEAD_URL = "headUrl";
    public static final String IMG_URL = "imgUrl";
    public static final String IMG_URL1 = "imgUrl1";
    public static final String IMG_URL2 = "imgUrl2";
    public static final String IMG_URL3 = "imgUrl3";
    public static final String IMG_URL4 = "imgUrl4";
    public static final String IMG_URL5 = "imgUrl5";
    public static final String IMG_URL6 = "imgUrl6";
    public static final String IMG_URL7 = "imgUrl7";
    public static final String IMG_URL8 = "imgUrl8";
    public static final String EXPERIENCE_ID = "experienceId";

    private static final String TAG = "ShareCoentenDbAdapter";
    private DatabaseHelper mDbHelper;
    private SQLiteDatabase mDb;
    private final Context context;

    private static class DatabaseHelper extends SQLiteOpenHelper {

        public DatabaseHelper(Context context) {

            super(context, DataBaseOpenHelper.DATABASE_NAME, null, DataBaseOpenHelper.DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + SQLITE_TABLE);
            onCreate(db);
        }
    }

    public ShareCoentenDB(Context context) {
        this.context = context;
    }

    public ShareCoentenDB open() throws SQLException {
        mDbHelper = new DatabaseHelper(context);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        if (mDbHelper != null) {
            mDbHelper.close();
        }
    }

    /**
     * 创建学生表的字段
     * 返回新增行主键id
     *
     * @param shareCoenten
     * @return
     */
    public int createShareCoenten(ShareCoenten shareCoenten) {
        long createResult = 0;
        int strid = 0;
        ContentValues initialValues = new ContentValues();
        initialValues.put(USER_NAME, shareCoenten.getUserName());
        initialValues.put(TIME, shareCoenten.getTime());
        initialValues.put(TYPE, shareCoenten.getType());
        initialValues.put(TITLE, shareCoenten.getTitle());
        initialValues.put(CONTENT, shareCoenten.getContent());
        initialValues.put(COLLECTION_COUNT, shareCoenten.getCollectioncount());
        initialValues.put(MESSAGE_COUNT, shareCoenten.getMessagecount());
        initialValues.put(SHARE_COUNT, shareCoenten.getSharecount());
        initialValues.put(HEAD_URL, shareCoenten.getHeadUrl());
        initialValues.put(MAJOR, shareCoenten.getMajor());
        initialValues.put(IMG_URL, shareCoenten.getImgUrl());
        initialValues.put(IMG_URL1, shareCoenten.getImgUrl1());
        initialValues.put(IMG_URL2, shareCoenten.getImgUrl2());
        initialValues.put(IMG_URL3, shareCoenten.getImgUrl3());
        initialValues.put(IMG_URL4, shareCoenten.getImgUrl4());
        initialValues.put(IMG_URL5, shareCoenten.getImgUrl5());
        initialValues.put(IMG_URL6, shareCoenten.getImgUrl6());
        initialValues.put(IMG_URL7, shareCoenten.getImgUrl7());
        initialValues.put(IMG_URL8, shareCoenten.getImgUrl8());
        initialValues.put(EXPERIENCE_ID, shareCoenten.getExperienceId());
        try {
            createResult = mDb.insert(SQLITE_TABLE, null, initialValues);

            Cursor cursor = mDb.rawQuery("select last_insert_rowid() from " + SQLITE_TABLE, null);
            if (cursor.moveToFirst())
                strid = cursor.getInt(0);

        } catch (Exception e) {
            // TODO: handle exception
        }
        return strid;
    }

    /**
     * 根据id删除
     *
     * @param id
     * @return
     */
    public boolean deleteById(String id) {
        int isDelete;
        String[] tName;
        tName = new String[]{id};
        isDelete = mDb.delete(SQLITE_TABLE, PK + "=?", tName);
        Log.e("deleteTicket", "isDelete:" + isDelete + "||" + "ticketID="
                + id);
        return isDelete > 0;
    }

    /**
     * 获取表中的所有字段
     *
     * @return
     */
    public ArrayList<ShareCoenten> fetchAll() {

        ArrayList<ShareCoenten> allTicketsList = new ArrayList<ShareCoenten>();
        Cursor mCursor = null;
        mCursor = mDb.query(SQLITE_TABLE, null, null, null, null, null, null);
        if (mCursor.moveToFirst()) {
            do {
                ShareCoenten shareCoentenDomain = new ShareCoenten();
                shareCoentenDomain.set_id(mCursor.getString(mCursor.getColumnIndex(PK)));
                shareCoentenDomain.setUserName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(USER_NAME)));
                shareCoentenDomain.setTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME)));
                shareCoentenDomain.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                shareCoentenDomain.setTitle(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TITLE)));
                shareCoentenDomain.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                shareCoentenDomain.setCollectioncount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(COLLECTION_COUNT)));
                shareCoentenDomain.setMessagecount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MESSAGE_COUNT)));
                shareCoentenDomain.setSharecount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SHARE_COUNT)));
                shareCoentenDomain.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));
                shareCoentenDomain.setImgUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL)));
                shareCoentenDomain.setImgUrl1(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL1)));
                shareCoentenDomain.setImgUrl2(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL2)));
                shareCoentenDomain.setImgUrl3(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL3)));
                shareCoentenDomain.setImgUrl4(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL4)));
                shareCoentenDomain.setImgUrl5(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL5)));
                shareCoentenDomain.setImgUrl6(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL6)));
                shareCoentenDomain.setImgUrl7(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL7)));
                shareCoentenDomain.setImgUrl8(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL8)));
                shareCoentenDomain.setExperienceId(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(EXPERIENCE_ID)));
                allTicketsList.add(shareCoentenDomain);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return allTicketsList;
    }

    /**
     * 根据用户名查询记录
     *
     * @param name
     * @return
     */
    public ArrayList<ShareCoenten> queryByWhere(String name) {
        ArrayList<ShareCoenten> shareCoentenDomains = new ArrayList<ShareCoenten>();
        Cursor mCursor = null;
        mCursor = mDb.query(SQLITE_TABLE, null,
                USER_NAME + "=?", new String[]{name}, null, null, null);
        if (mCursor.moveToFirst()) {
            do {
                String time = mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME));
                String toNow = "";
                try {
                    SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
                    Date date = format.parse(time);
                    Date now = new Date();
                    PrettyTime t = new PrettyTime(now);
                    toNow = t.format(date);
                    Log.d("距现在多久：", toNow);
                } catch (Exception e) {

                }

                ShareCoenten shareCoentenDomain = new ShareCoenten();
                shareCoentenDomain.set_id(mCursor.getString(mCursor.getColumnIndex(PK)));
                shareCoentenDomain.setUserName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(USER_NAME)));
                shareCoentenDomain.setTime(toNow);
                shareCoentenDomain.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                shareCoentenDomain.setTitle(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TITLE)));
                shareCoentenDomain.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                shareCoentenDomain.setCollectioncount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(COLLECTION_COUNT)));
                shareCoentenDomain.setMessagecount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MESSAGE_COUNT)));
                shareCoentenDomain.setSharecount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SHARE_COUNT)));
                shareCoentenDomain.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));
                shareCoentenDomain.setImgUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL)));
                shareCoentenDomain.setImgUrl1(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL1)));
                shareCoentenDomain.setImgUrl2(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL2)));
                shareCoentenDomain.setImgUrl3(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL3)));
                shareCoentenDomain.setImgUrl4(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL4)));
                shareCoentenDomain.setImgUrl5(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL5)));
                shareCoentenDomain.setImgUrl6(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL6)));
                shareCoentenDomain.setImgUrl7(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL7)));
                shareCoentenDomain.setImgUrl8(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL8)));
                shareCoentenDomain.setExperienceId(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(EXPERIENCE_ID)));
                shareCoentenDomain.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                shareCoentenDomains.add(shareCoentenDomain);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return shareCoentenDomains;
    }

    /**
     * 查询分享内容
     * 并统计该分享内容的分享数，收藏数，留言数
     *
     * @return
     */
    public ArrayList<ShareCoenten> queryAll() {
        ArrayList<ShareCoenten> shareCoentens = new ArrayList<ShareCoenten>();

        String sql = "select t._id," +
                "t.userName," +
                "t.content," +
                "t.headUrl," +
                "t.time," +
                "t.title," +
                "t.type," +
                "t.major," +
                "t.imgUrl," +
                "t.imgUrl1," +
                "t.imgUrl2," +
                "t.imgUrl3," +
                "t.imgUrl4," +
                "t.imgUrl5," +
                "t.imgUrl6," +
                "t.imgUrl7," +
                "t.imgUrl8," +
                "t.experienceId," +
                "(select count(1) from collection c where t._id = c.relationId) as collectioncount," +
                "(select count(1) from myShare m where t._id = m.relationId) as sharecount," +
                "(select count(1) from share_message s where t._id = s.shareCoentenId) as messagecount from share_coent t";
        Cursor mCursor = null;
        mCursor = mDb.rawQuery(sql, null);
        if (mCursor.moveToFirst()) {
            do {
                String time = mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME));
                String toNow = "";
                try {
                    SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
                    Date date = format.parse(time);
                    Date now = new Date();
                    PrettyTime t = new PrettyTime(now);
                    toNow = t.format(date);
                    Log.d("距现在多久：", toNow);
                } catch (Exception e) {

                }

                ShareCoenten shareCoenten = new ShareCoenten();
                shareCoenten.set_id(mCursor.getString(mCursor.getColumnIndex(PK)));
                shareCoenten.setUserName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(USER_NAME)));
                shareCoenten.setTime(toNow);
                shareCoenten.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                shareCoenten.setTitle(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TITLE)));
                shareCoenten.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                shareCoenten.setCollectioncount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(COLLECTION_COUNT)));
                shareCoenten.setMessagecount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MESSAGE_COUNT)));
                shareCoenten.setSharecount(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(SHARE_COUNT)));
                shareCoenten.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));
                shareCoenten.setImgUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL)));
                shareCoenten.setImgUrl1(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL1)));
                shareCoenten.setImgUrl2(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL2)));
                shareCoenten.setImgUrl3(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL3)));
                shareCoenten.setImgUrl4(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL4)));
                shareCoenten.setImgUrl5(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL5)));
                shareCoenten.setImgUrl6(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL6)));
                shareCoenten.setImgUrl7(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL7)));
                shareCoenten.setImgUrl8(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL8)));
                shareCoenten.setExperienceId(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(EXPERIENCE_ID)));
                shareCoenten.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                shareCoentens.add(shareCoenten);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return shareCoentens;
    }

    /**
     * 查询我收藏的内容
     *
     * @param userName
     * @return
     */
    public ArrayList<ShareCoenten> queryMyCollect(String userName) {
        ArrayList<ShareCoenten> shareCoentens = new ArrayList<ShareCoenten>();
        Cursor mCursor = null;

        String sql = "select t2._id," +
                "t2.userName," +
                "t2.content," +
                "t2.headUrl," +
                "t2.time," +
                "t2.title," +
                "t2.type," +
                "t2.major," +
                "t2.imgUrl," +
                "t2.imgUrl1," +
                "t2.imgUrl2," +
                "t2.imgUrl3," +
                "t2.imgUrl4," +
                "t2.imgUrl5," +
                "t2.imgUrl6," +
                "t2.imgUrl7," +
                "t2.imgUrl8," +
                "t2.experienceId from "
                + CollectionDB.SQLITE_TABLE + " t1,"
                + SQLITE_TABLE + " t2 where t1." + CollectionDB.RELATION_ID
                + "= t2." + PK + " and t1." + CollectionDB.USER_NAME + "= '" + userName + "'";

        mCursor = mDb.rawQuery(sql, null);
        if (mCursor.moveToFirst()) {
            do {
                ShareCoenten shareCoenten = new ShareCoenten();
                shareCoenten.set_id(mCursor.getString(mCursor.getColumnIndex(PK)));
                shareCoenten.setUserName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(USER_NAME)));
                shareCoenten.setTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME)));
                shareCoenten.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                shareCoenten.setTitle(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TITLE)));
                shareCoenten.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                shareCoenten.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));
                shareCoenten.setImgUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL)));
                shareCoenten.setImgUrl1(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL1)));
                shareCoenten.setImgUrl2(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL2)));
                shareCoenten.setImgUrl3(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL3)));
                shareCoenten.setImgUrl4(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL4)));
                shareCoenten.setImgUrl5(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL5)));
                shareCoenten.setImgUrl6(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL6)));
                shareCoenten.setImgUrl7(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL7)));
                shareCoenten.setImgUrl8(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL8)));
                shareCoenten.setExperienceId(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(EXPERIENCE_ID)));
                shareCoenten.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                shareCoentens.add(shareCoenten);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return shareCoentens;
    }

    /**
     * 查询我分享的内容
     *
     * @param userName
     * @return
     */
    public ArrayList<ShareCoenten> queryMyShare(String userName) {
        ArrayList<ShareCoenten> shareCoentens = new ArrayList<ShareCoenten>();
        Cursor mCursor = null;

        String sql = "select t2._id," +
                "t2.userName," +
                "t2.content," +
                "t2.headUrl," +
                "t2.time," +
                "t2.title," +
                "t2.type," +
                "t2.major," +
                "t2.imgUrl," +
                "t2.imgUrl1," +
                "t2.imgUrl2," +
                "t2.imgUrl3," +
                "t2.imgUrl4," +
                "t2.imgUrl5," +
                "t2.imgUrl6," +
                "t2.imgUrl7," +
                "t2.imgUrl8," +
                "t2.experienceId from "
                + MyShareDB.SQLITE_TABLE + " t1,"
                + SQLITE_TABLE + " t2 where t1." + MyShareDB.RELATION_ID
                + "= t2." + PK + " and t1." + MyShareDB.USER_NAME + "= '" + userName + "'";

        mCursor = mDb.rawQuery(sql, null);
        if (mCursor.moveToFirst()) {
            do {
                ShareCoenten shareCoenten = new ShareCoenten();
                shareCoenten.set_id(mCursor.getString(mCursor.getColumnIndex(PK)));
                shareCoenten.setUserName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(USER_NAME)));
                shareCoenten.setTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME)));
                shareCoenten.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                shareCoenten.setTitle(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TITLE)));
                shareCoenten.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                shareCoenten.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));
                shareCoenten.setImgUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL)));
                shareCoenten.setImgUrl1(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL1)));
                shareCoenten.setImgUrl2(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL2)));
                shareCoenten.setImgUrl3(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL3)));
                shareCoenten.setImgUrl4(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL4)));
                shareCoenten.setImgUrl5(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL5)));
                shareCoenten.setImgUrl6(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL6)));
                shareCoenten.setImgUrl7(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL7)));
                shareCoenten.setImgUrl8(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL8)));
                shareCoenten.setExperienceId(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(EXPERIENCE_ID)));
                shareCoenten.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                shareCoentens.add(shareCoenten);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return shareCoentens;
    }

    public ArrayList<ShareCoenten> queryByWheres(String searchContent) {
        ArrayList<ShareCoenten> shareCoentens = new ArrayList<ShareCoenten>();
        Cursor mCursor = null;

        String sql = "select distinct t2._id," +
                "t2.userName," +
                "t2.content," +
                "t2.headUrl," +
                "t2.time," +
                "t2.title," +
                "t2.type," +
                "t2.major," +
                "t2.imgUrl," +
                "t2.imgUrl1," +
                "t2.imgUrl2," +
                "t2.imgUrl3," +
                "t2.imgUrl4," +
                "t2.imgUrl5," +
                "t2.imgUrl6," +
                "t2.imgUrl7," +
                "t2.imgUrl8," +
                "t2.experienceId from "
                + SQLITE_TABLE + " t2," + UserDB.SQLITE_TABLE + " t1 "
                + "where t1." + UserDB.LOGIN_NAME + "=t2." + USER_NAME
                + " and (t2." + USER_NAME
                + " like '%" + searchContent + "%' or t2." + CONTENT
                + " like '%" + searchContent + "%' or t2." + TITLE
                + " like '%" + searchContent + "%' or t1." + UserDB.LOGIN_NAME
                + " like '%" + searchContent + "%' or t1." + UserDB.MAJOR
                + " like '%" + searchContent + "%' or t1." + UserDB.SCHOOL
                + " like '%" + searchContent + "%')";

        mCursor = mDb.rawQuery(sql, null);
        if (mCursor.moveToFirst()) {
            do {
                ShareCoenten shareCoenten = new ShareCoenten();
                shareCoenten.set_id(mCursor.getString(mCursor.getColumnIndex(PK)));
                shareCoenten.setUserName(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(USER_NAME)));
                shareCoenten.setTime(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TIME)));
                shareCoenten.setType(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TYPE)));
                shareCoenten.setTitle(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(TITLE)));
                shareCoenten.setContent(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(CONTENT)));
                shareCoenten.setHeadUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(HEAD_URL)));
                shareCoenten.setImgUrl(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL)));
                shareCoenten.setImgUrl1(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL1)));
                shareCoenten.setImgUrl2(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL2)));
                shareCoenten.setImgUrl3(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL3)));
                shareCoenten.setImgUrl4(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL4)));
                shareCoenten.setImgUrl5(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL5)));
                shareCoenten.setImgUrl6(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL6)));
                shareCoenten.setImgUrl7(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL7)));
                shareCoenten.setImgUrl8(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(IMG_URL8)));
                shareCoenten.setExperienceId(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(EXPERIENCE_ID)));
                shareCoenten.setMajor(mCursor.getString(mCursor
                        .getColumnIndexOrThrow(MAJOR)));
                shareCoentens.add(shareCoenten);
            } while (mCursor.moveToNext());
        }
        if (mCursor != null && !mCursor.isClosed()) {
            mCursor.close();
        }
        return shareCoentens;
    }

    public void updateHead(ShareCoenten shareCoenten) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(HEAD_URL, shareCoenten.getHeadUrl());

        int updateIndex = mDb.update(SQLITE_TABLE, initialValues, USER_NAME + "=?", new String[]{shareCoenten.getUserName()});
    }
}
